package net.tngou.iask.dao;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

import net.tngou.iask.entity.Statistic;
import net.tngou.iask.jdbc.QueryHelper;
import net.tngou.iask.util.DateUtil;

public class StatisticDao extends BaseDao {

	
	/**
	 * 取得最近多少天新增患者
	 * @param main 实体
	 * @param date 天数
	 * @return
	 */
	public int patientDate(long main,int date) {
		int r=0;
		if(main==0)
		{
			String sql="SELECT COUNT(*) FROM `iask_patient` WHERE DATE_SUB(NOW(),INTERVAL ? DAY) <=time";
			r=(int) QueryHelper.stat(sql, date);
		}else
		{
			String sql="SELECT COUNT(*) FROM `iask_patient_main` patient_main , `iask_patient` patient WHERE patient.id=patient_main.patient AND patient_main.main=? AND DATE_SUB(NOW(),INTERVAL ? DAY) <=patient.time";
			r=(int) QueryHelper.stat(sql, main,date);
		}
		return r;
	}
	
	
	/**
	 * 取得两者时间的数据
	 * @param main
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public int patientTotal(long main,LocalDateTime startDate,LocalDateTime endDate) {
		int r=0;
		if(main==0)
		{
			String sql="SELECT COUNT(*) FROM `iask_patient` WHERE time BETWEEN str_to_date(?, '%Y-%m-%d %H:%i:%s') AND str_to_date(?, '%Y-%m-%d %H:%i:%s')";
			r=(int) QueryHelper.stat(sql, DateUtil.format(startDate, "yyyy-MM-dd 00:00:00"), DateUtil.format(endDate, "yyyy-MM-dd 23:59:59"));
		}else
		{
			String sql="SELECT COUNT(*) FROM `iask_patient_main` patient_main , `iask_patient` patient WHERE patient.id=patient_main.patient AND patient_main.main=? AND patient.time  BETWEEN str_to_date(?, '%Y-%m-%d %H:%i:%s') AND str_to_date(?, '%Y-%m-%d %H:%i:%s')";
			r=(int) QueryHelper.stat(sql, main,DateUtil.format(startDate, "yyyy-MM-dd 00:00:00"), DateUtil.format(endDate, "yyyy-MM-dd 23:59:59"));
		}
		return r;
		
	}
	
	/**
	 * 年龄阶段
	 * @param main
	 * @param starAge
	 * @param endAge
	 * @return
	 */
	public int patientAge(long main,int starAge,int endAge) {
		int r=0;
		if(main==0)
		{
			String sql="SELECT COUNT(*) FROM `iask_patient` WHERE birthday BETWEEN DATE_SUB(NOW(),INTERVAL ? YEAR) AND   DATE_SUB(NOW(),INTERVAL ? YEAR)";
			r=(int) QueryHelper.stat(sql,endAge, starAge);
		}else
		{
			String sql="SELECT COUNT(*) FROM `iask_patient_main` patient_main , `iask_patient` patient WHERE patient.id=patient_main.patient AND patient_main.main=? AND patient.birthday BETWEEN DATE_SUB(NOW(),INTERVAL ? YEAR) AND   DATE_SUB(NOW(),INTERVAL ? YEAR)";
			r=(int) QueryHelper.stat(sql, main,endAge,starAge);
		}
		return r;
	}
	
	/**
	 * 患者数
	 * @param main
	 * @return
	 */
	public int patientTotal(long main) {
		int r=0;
		if(main==0)
		{
			String sql="SELECT COUNT(id) FROM `iask_patient` ";
			r=(int) QueryHelper.stat(sql);
		}else
		{
			String sql="SELECT COUNT(*) FROM `iask_patient_main` patient_main ,`iask_patient` patient WHERE patient.id=patient_main.patient AND patient_main.main=? ";
			r=(int) QueryHelper.stat(sql,main);
		}
		return r;
	}
	
	/**
	 * 性别
	 * @param main
	 * @param gender
	 * @return
	 */
	public int patientGender(long main,int gender) {
		int r=0;
		if(main==0)
		{
			String sql="SELECT COUNT(id) FROM `iask_patient` WHERE gender=?";
			r=(int) QueryHelper.stat(sql,gender);
		}else
		{
			String sql="SELECT COUNT(*) FROM `iask_patient_main` patient_main ,`iask_patient` patient WHERE patient.id=patient_main.patient AND patient_main.main=? AND patient.gender=? ";
			r=(int) QueryHelper.stat(sql,main,gender);
		}
		return r;
	}
	
	
	/**
	 * 取得最近多少天新增治疗
	 * @param main 实体
	 * @param date 天数
	 * @return
	 */
	public int cureDate(long main,int date) {
		int r=0;
		String sql="SELECT COUNT(*) FROM `iask_cure` WHERE DATE_SUB(NOW(),INTERVAL ? DAY) <=time";
		if(main!=0)
		{
			sql="SELECT COUNT(*) FROM `iask_cure` WHERE main=? AND DATE_SUB(NOW(),INTERVAL ? DAY) <=time";
			r=(int) QueryHelper.stat(sql,main, date);
		}else
		{
			
			r=(int) QueryHelper.stat(sql, date);
		}
		
		return r;
	}
	
	public int cureDate(long main,long disease,int date) {
		int r=0;
		String sql="SELECT COUNT(*) FROM `iask_cure` WHERE disease=? AND DATE_SUB(NOW(),INTERVAL ? DAY) <=time";
		if(main!=0)
		{
			sql="SELECT COUNT(*) FROM `iask_cure` WHERE main=? AND disease=? AND DATE_SUB(NOW(),INTERVAL ? DAY) <=time";
			r=(int) QueryHelper.stat(sql,main,disease, date);
		}else
		{
			
			r=(int) QueryHelper.stat(sql, disease,date);
		}
		
		return r;
	}
	
	/**
	 * 治疗情况
	 * @param main
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public int cureTotal(long main,LocalDateTime startDate,LocalDateTime endDate) {
		int r=0;
		String sql="SELECT COUNT(*) FROM `iask_cure` WHERE time BETWEEN str_to_date(?, '%Y-%m-%d %H:%i:%s') AND str_to_date(?, '%Y-%m-%d %H:%i:%s')";
		if(main!=0)
		{
			sql="SELECT COUNT(*) FROM `iask_cure` WHERE main=? AND time BETWEEN str_to_date(?, '%Y-%m-%d %H:%i:%s') AND str_to_date(?, '%Y-%m-%d %H:%i:%s')";
			r=(int) QueryHelper.stat(sql,main, DateUtil.format(startDate, "yyyy-MM-dd 00:00:00"), DateUtil.format(endDate, "yyyy-MM-dd 23:59:59"));
		}else
		{
			
			r=(int) QueryHelper.stat(sql, DateUtil.format(startDate, "yyyy-MM-dd 00:00:00"), DateUtil.format(endDate, "yyyy-MM-dd 23:59:59"));
		}
		
		return r;
	}
	
	/**
	 * 疾病对应的发布位置
	 * @param main
	 * @return
	 */
	public int curePlace(long main) {
		int r=0;
		String sql="SELECT COUNT(place) FROM `iask_cure_place` GROUP BY place";
		if(main!=0)
		{
			sql="SELECT COUNT(cure_place.place) FROM  `iask_cure_place` cure_place, `iask_cure` cure WHERE cure_place.cure=cure.id AND cure.main=?  GROUP BY cure_place.place";
			r=(int) QueryHelper.stat(sql,main);
		}else
		{		
			r=(int) QueryHelper.stat(sql);
		}	
		return r;
	}
	
	/**
	 * 治疗对应科室
	 * @param main
	 * @return
	 */
	public int cureDepartment(long main) {
		int r=0;
		String sql="SELECT COUNT(department) FROM `iask_cure_department` GROUP BY department";
		if(main!=0)
		{
			sql="SELECT COUNT(cure_department.department) FROM  `iask_cure_department` cure_department, `iask_cure` cure WHERE cure_department.cure=cure.id AND cure.main=?  GROUP BY cure_department.department";
			r=(int) QueryHelper.stat(sql,main);
		}else
		{		
			r=(int) QueryHelper.stat(sql);
		}	
		return r;
	}
	
	/**
	 * 治疗对应方式
	 * @param main
	 * @return
	 */
	public int cureTreatment(long main) {
		int r=0;
		String sql="SELECT COUNT(treatment) FROM `iask_cure_treatment` GROUP BY treatment";
		if(main!=0)
		{
			sql="SELECT COUNT(cure_treatment.treatment) FROM  `iask_cure_treatment` cure_treatment, `iask_cure` cure WHERE cure_treatment.cure=cure.id AND cure.main=?  GROUP BY cure_treatment.treatment";
			r=(int) QueryHelper.stat(sql,main);
		}else
		{		
			r=(int) QueryHelper.stat(sql);
		}	
		return r;
	}
	
	/**
	 * 取得治疗疾病 对应用户数
	 * @param main
	 * @return
	 */
	public List<Statistic> getCureList(long main) {
		String sql="SELECT disease.name,ct.value FROM ( SELECT disease,COUNT(*) as value FROM `iask_cure` GROUP BY disease ) ct,`iask_disease` disease WHERE disease.id=ct.disease ORDER BY ct.value DESC ";
		if(main!=0)
		{
			sql="SELECT disease.name,ct.value FROM ( SELECT disease,COUNT(*) as value FROM `iask_cure` WHERE main=? GROUP BY disease ) ct,`iask_disease` disease WHERE disease.id=ct.disease ORDER BY ct.value DESC ";
			return QueryHelper.query(Statistic.class, sql, main);
		}
		return QueryHelper.query(Statistic.class, sql);
	}
	
	/**
	 * 取得最近几天的状态
	 * @param main 
	 * @param start 计算天数 
	 * @param date 天数
	 * @param state 状态 
	 * @return
	 */
	public int getAccess(long main,int start,int date,int state) {
		
		String sql="SELECT COUNT(*) FROM `iask_access` WHERE   time  BETWEEN DATE_SUB(NOW(),INTERVAL ? DAY) AND DATE_SUB(NOW(),INTERVAL ? DAY) AND state=?";
		if(main==0)
			return (int) QueryHelper.stat(sql, start+date,start,state);
		else
		{
			sql="SELECT COUNT(*) FROM `iask_access` WHERE main=? AND  time  BETWEEN DATE_SUB(NOW(),INTERVAL ? DAY) AND DATE_SUB(NOW(),INTERVAL ? DAY) AND state=?";
			return (int) QueryHelper.stat(sql,main, start+date,start,state);
		}	
	}
    public int getAccess(long main,int start,int date) {
		
		String sql="SELECT COUNT(*) FROM `iask_access` WHERE   time  BETWEEN DATE_SUB(NOW(),INTERVAL ? DAY) AND DATE_SUB(NOW(),INTERVAL ? DAY) ";
		if(main==0)
			return (int) QueryHelper.stat(sql, start+date,start);
		else
		{
			sql="SELECT COUNT(*) FROM `iask_access` WHERE main=? AND  time  BETWEEN DATE_SUB(NOW(),INTERVAL ? DAY) AND DATE_SUB(NOW(),INTERVAL ? DAY) ";
			return (int) QueryHelper.stat(sql,main, start+date,start);
		}	
	}
    
    public int accessDate(long main,int date) {
		
  		String sql="SELECT COUNT(*) FROM `iask_access` WHERE   time  >= DATE_SUB(NOW(),INTERVAL ? DAY)  ";
  		if(main==0)
  			return (int) QueryHelper.stat(sql, date);
  		else
  		{
  			sql="SELECT COUNT(*) FROM `iask_access` WHERE main=? AND  time  >= DATE_SUB(NOW(),INTERVAL ? DAY) ";
  			return (int) QueryHelper.stat(sql,main, date);
  		}	
  	}
    
    public int accessDate(long main,long disease,int date) {
		
  		String sql="SELECT COUNT(*) FROM `iask_access` access, `iask_cure` cure WHERE access.cure=cure.id AND  cure.disease=? AND  access.time  >= DATE_SUB(NOW(),INTERVAL ? DAY)  ";
  		if(main==0)
  			return (int) QueryHelper.stat(sql,disease, date);
  		else
  		{
  			sql="SELECT COUNT(*) FROM `iask_access` access, `iask_cure` cure WHERE access.cure=cure.id  AND access.main=?  AND  cure.disease=?  AND  access.time  >= DATE_SUB(NOW(),INTERVAL ? DAY) ";
  			return (int) QueryHelper.stat(sql,main,disease, date);
  		}	
  	}
    
  public int accessTotal(long main,long disease) {
		
  		String sql="SELECT COUNT(*) FROM `iask_access` access, `iask_cure` cure WHERE access.cure=cure.id AND  cure.disease=?  ";
  		if(main==0)
  			return (int) QueryHelper.stat(sql,disease);
  		else
  		{
  			sql="SELECT COUNT(*) FROM `iask_access` access, `iask_cure` cure WHERE access.cure=cure.id  AND access.main=?  AND  cure.disease=?  ";
  			return (int) QueryHelper.stat(sql,main,disease);
  		}	
  	}
  public int accessTotal(long main,long disease,int state) {
		
		String sql="SELECT COUNT(*) FROM `iask_access` access, `iask_cure` cure WHERE access.cure=cure.id AND  cure.disease=?  AND state=? ";
		if(main==0)
			return (int) QueryHelper.stat(sql,disease,state);
		else
		{
			sql="SELECT COUNT(*) FROM `iask_access` access, `iask_cure` cure WHERE access.cure=cure.id  AND access.main=?  AND  cure.disease=?  AND state=?  ";
			return (int) QueryHelper.stat(sql,main,disease,state);
		}	
	}
    public int accessTotal(long main,LocalDateTime startDate,LocalDateTime endDate) {
		int r=0;
		String sql="SELECT COUNT(*) FROM `iask_access` WHERE time BETWEEN str_to_date(?, '%Y-%m-%d %H:%i:%s') AND str_to_date(?, '%Y-%m-%d %H:%i:%s')";
		if(main!=0)
		{
			sql="SELECT COUNT(*) FROM `iask_access` WHERE main=? AND time BETWEEN str_to_date(?, '%Y-%m-%d %H:%i:%s') AND str_to_date(?, '%Y-%m-%d %H:%i:%s')";
			r=(int) QueryHelper.stat(sql,main, DateUtil.format(startDate, "yyyy-MM-dd 00:00:00"), DateUtil.format(endDate, "yyyy-MM-dd 23:59:59"));
		}else
		{
			
			r=(int) QueryHelper.stat(sql, DateUtil.format(startDate, "yyyy-MM-dd 00:00:00"), DateUtil.format(endDate, "yyyy-MM-dd 23:59:59"));
		}
		
		return r;
	}
	
}
